import xlrd
import os
from web.log.log import logger
from web.config.config import Config
class ExcelUtil():

    def __init__(self,filename):
        #判断filename是否存在，从默认的数据存储目录下读取
        if not os.path.exists(filename):
            filename = Config().dataPath + '/' + filename
        if not os.path.exists(filename):
            logger.error(f'[{filename}] 文件不存在')
            raise BaseException(filename + ' is not exists')

        self.workbook = xlrd.open_workbook(filename, formatting_info=True)

    def get_cell_value(self, sheet_index_or_name, row_index, col_index):
        sheet = self.__get_sheet(sheet_index_or_name)
        if sheet.nrows and sheet.ncols:
            return sheet.cell_value(row_index, col_index)
        else:
            raise BaseException("Index out of range!")

    def __get_sheet(self, sheet_index_or_name):
        """
        根据sheet的索引或名称，获取sheet对象
        :param sheet_index_or_name: sheet的索引或名称
        :return:sheet对象
        """
        if isinstance(sheet_index_or_name, int):
            if len(self.workbook.sheet_names()) > sheet_index_or_name:
                return self.workbook.sheet_by_index(sheet_index_or_name)
            else:
                raise BaseException("Invalid Sheet Index!")
        elif isinstance(sheet_index_or_name, str):
            if sheet_index_or_name in self.workbook.sheet_names():
                return self.workbook.sheet_by_name(sheet_index_or_name)
            else:
                raise BaseException("Invalid Sheet Name!")

    def get_rows_num(self,sheet_index_or_name):
        return self.__get_sheet(sheet_index_or_name).nrows

    def get_cols_num(self,sheet_index_or_name):
        return self.__get_sheet(sheet_index_or_name).ncols

    def get_sheet_names(self):
        return self.workbook.sheet_names()

if __name__ == '__main__':
    excel = ExcelUtil('login_data.xls')

    #
    rows = excel.get_rows_num(0)
    cols = excel.get_cols_num(0)
    datalist = []
    for row in range(1,rows):
        caselist = []
        for col in range(1,cols):
            value = excel.get_cell_value(0,row,col)
            caselist.append(value)

        datalist.append(tuple(caselist))
    print(datalist)